﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MSSQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.SQLserver
{
    public class AllMarket : IAllMarket
    {
        private SqlConnection _internalConnection;
        private SqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        private const string PARM_TRANSDATE = "@TRANSDATE";
        private const string PARM_ORDERNO = "@ORDERNO";
        private const string PARM_SIDE = "@SIDE";
        private const string PARM_SYMBOL = "@SYMBOL";
        private const string PARM_PRICE = "@PRICE";
        private const string PARM_VOLUME = "@VOLUME";
        private const string PARM_MATCHED = "@MATCHED";
        private const string PARM_PC = "@PC";
        private const string PARM_ACCOUNTNO = "@ACCOUNTNO";
        private const string PARM_STATUS = "@STATUS";
        private const string PARM_TID = "@TID";
        private const string PARM_TNO = "@TNO";
        private const string PARM_TIME = "@TIME";
        private const string PARM_SHORTNAME = "@SHORTNAME";

        private const string HOSE_MARKET_SUMMARY = "SELECT SUM(VOLUME)/2 AS SUM_VOL, SUM(MATCHED)/2 AS SUM_MVOL, SUM(VAL)/2 AS SUM_VAL, SUM(MVAL)/2 AS SUM_MVAL FROM V_HOSEMARKET WHERE ('1'= '1') ";

        private const string DAILYORDER_SUMMARY = "SELECT     SUM(VOLUME) AS SUM_VOL, SUM(MATCHED) AS SUM_MVOL FROM         V_DailyOrder WHERE     ('1' = '1' )";

        private const string DAILYORDER = "SELECT * FROM V_DailyOrder WHERE ('1'= '1') ";

        private const string HOSE_MARKET_ORDERS = "SELECT * FROM V_HOSEMARKET WHERE ('1'= '1') ";

        public void InsertAllMarket(string orderNo, string side, string symbol, string price, long volume, long MATCHED, string PC, string accountNo, string status, string TID, string Tno, string time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter(PARM_ORDERNO , SqlDbType.VarChar,10),
                        new SqlParameter(PARM_SIDE, SqlDbType.VarChar, 1),
                        new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar,8),
                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar,6),
                        new SqlParameter(PARM_VOLUME, SqlDbType.BigInt),
                        new SqlParameter(PARM_MATCHED, SqlDbType.BigInt),
                        new SqlParameter(PARM_PC, SqlDbType.VarChar,1),
                        new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar,10),
                        new SqlParameter(PARM_STATUS, SqlDbType.VarChar,1),
                        new SqlParameter(PARM_TID, SqlDbType.VarChar,3),
                        new SqlParameter(PARM_TNO, SqlDbType.VarChar,3),
                        new SqlParameter(PARM_TIME, SqlDbType.VarChar,6)
                };
                parms[0].Value = orderNo;
                parms[1].Value = side;
                parms[2].Value = symbol;
                parms[3].Value = price;
                parms[4].Value = volume;
                parms[5].Value = MATCHED;
                parms[6].Value = PC;
                parms[7].Value = accountNo;
                parms[8].Value = status;
                parms[9].Value = TID;
                parms[10].Value = Tno;
                parms[11].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertAllMarket", parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertBackupAllmarket(DateTime transDate, string orderNo, string side, string symbol, string price, long volume, long MATCHED, string PC, string accountNo, string status, string TID, string Tno, string time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter(PARM_TRANSDATE , SqlDbType.DateTime),
                        new SqlParameter(PARM_ORDERNO , SqlDbType.VarChar,10),
                        new SqlParameter(PARM_SIDE, SqlDbType.VarChar, 1),
                        new SqlParameter(PARM_SYMBOL, SqlDbType.VarChar,8),
                        new SqlParameter(PARM_PRICE, SqlDbType.VarChar,6),
                        new SqlParameter(PARM_VOLUME, SqlDbType.BigInt),
                        new SqlParameter(PARM_MATCHED, SqlDbType.BigInt),
                        new SqlParameter(PARM_PC, SqlDbType.VarChar,1),
                        new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar,10),
                        new SqlParameter(PARM_STATUS, SqlDbType.VarChar,1),
                        new SqlParameter(PARM_TID, SqlDbType.VarChar,3),
                        new SqlParameter(PARM_TNO, SqlDbType.VarChar,3),
                        new SqlParameter(PARM_TIME, SqlDbType.VarChar,6)
                };
                parms[0].Value = transDate;
                parms[1].Value = orderNo;
                parms[2].Value = side;
                parms[3].Value = symbol;
                parms[4].Value = price;
                parms[5].Value = volume;
                parms[6].Value = MATCHED;
                parms[7].Value = PC;
                parms[8].Value = accountNo;
                parms[9].Value = status;
                parms[10].Value = TID;
                parms[11].Value = Tno;
                parms[12].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertBackupAllMarket", parms);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Convert raw data and insert in to HoseMarket table base on MarketData and SessionInfo
        /// </summary>
        /// <param name="transDate">Transaction date</param>
        public void InsertHoseMarketFromRawData(DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertHoseMarketFromRawData", parm1);
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// Select Stockno from HoseSession table
        /// </summary>
        /// <param name="transDate">transaction date</param>
        /// <returns></returns>
        public List<int> SelectStockNoByTransDate(DateTime transDate)
        {
            List<int> stockNo = new List<int>();
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SqlDataReader rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectHoseSession_SymbolByTransDate", parm1);
                while (rdr.Read())
                {
                    stockNo.Add(Convert.ToInt32(rdr["STOCKNO"]));
                }
                return stockNo;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Delete backup record on transaction date in Backup_AllMarket table
        /// </summary>
        /// <param name="transDate">transaction date</param>
        /// <returns></returns>
        public void DeleteBackupAllMarket(DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteBackupAllMarket", parm1);
            }
            catch
            {
                throw;
            }

        }

        public List<SecuritiesCompany> getSecuritiesCompany()
        {
            try
            {
                List<SecuritiesCompany> list = new List<SecuritiesCompany>();
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectSecuritiesCompany");
                while (rdr.Read())
                {
                    SecuritiesCompany item = new SecuritiesCompany(rdr["TID"].ToString(),
                                rdr["NAME"].ToString(),
                                rdr["SHORTNAME"].ToString(),
                                rdr["ADDRESS"].ToString(),
                                rdr["WEBSITE"].ToString(),
                                rdr["MEMBERSINCE"].Equals(null) ? DateTime.Today.Date : Convert.ToDateTime(rdr["MEMBERSINCE"]));
                    list.Add(item);
                }
                return list;
            }
            catch
            {
                throw;
            }
        }

        public SecuritiesCompany getSecurityCompanyByTID(string TID)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TID, SqlDbType.VarChar, 20);
                parm1.Value = (TID == null) ? string.Empty : TID;

                rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectSecuritiesCompanyByTID", parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new SecuritiesCompany(rdr["TID"].ToString(),
                                rdr["NAME"].ToString(),
                                rdr["SHORTNAME"].ToString(),
                                rdr["ADDRESS"].ToString(),
                                rdr["WEBSITE"].ToString(),
                                rdr["MEMBERSINCE"].Equals(null) ? DateTime.Today.Date : Convert.ToDateTime(rdr["MEMBERSINCE"]));
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }
        public SecuritiesCompany getSecurityCompanyByShortName(string ShortName)
        {
            SqlDataReader rdr = null;
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_SHORTNAME, SqlDbType.VarChar, 20);
                parm1.Value = ShortName;

                rdr = SQLHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "SelectSecuritiesCompanyByShortName", parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new SecuritiesCompany(rdr["TID"].ToString(),
                                rdr["NAME"].ToString(),
                                rdr["SHORTNAME"].ToString(),
                                rdr["ADDRESS"].ToString(),
                                rdr["WEBSITE"].ToString(),
                                rdr["MEMBERSINCE"].Equals(null) ? DateTime.Today.Date : Convert.ToDateTime(rdr["MEMBERSINCE"]));
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }


        public List<HoseMarketData> getHoseMarketOrders(DateTime fromDate, DateTime toDate, string orderNo, string side, string symbol, long HighPrice, long LowPrice, long HighQtty, long LowQtty, long HighMQtty, long LowMQtty, string PC, string accountNo, string status, string TID, string Tno, string orderType, int fromTime, int toTime, double HighVal, double LowVal, double HighmVal, double LowmVal)
        {
            try
            {
                string query = HOSE_MARKET_ORDERS +
                    parmBuilderTransDate(fromDate, toDate) +
                    parmBuilderOrderNo(orderNo) +
                    parmBuilderOrderSide(side) +
                    parmBuilderStockSymbol(symbol) +
                    parmBuilderPrice(HighPrice, LowPrice) +
                    parmBuilderPC(PC) + parmBuilderAccountNo(accountNo) +
                    parmBuilderStatus(status) + parmBuilderTID(TID) +
                    parmBuilderTno(Tno) + parmBuilderTime(fromTime, toTime) +
                    parmBuilderOrderType(orderType) +
                    parmBuilderMQtty(HighMQtty, LowMQtty) +
                    parmBuilderMVal(HighmVal, LowmVal) +
                    parmBuilderVal(HighVal, LowVal);
                List<HoseMarketData> list = new List<HoseMarketData>();
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);

                //SqlDataAdapter da = new SqlDataAdapter(query, _internalConnection);
                //DataSet result = new DataSet();
                //da.Fill(result, "HoseMarketOrders");

                while (rdr.Read())
                {
                    HoseMarketData item = new HoseMarketData(
                        Convert.ToDateTime(rdr["TransDate"].Equals(DBNull.Value) ? 0 : rdr["TransDate"]),
                        rdr["OrderNo"].ToString(),
                        rdr["Side"].ToString(),
                        rdr["Symbol"].ToString(),
                        Convert.ToInt64(rdr["Price"].Equals(DBNull.Value) ? 0 : rdr["Price"]),
                        rdr["PC"].ToString(),
                        rdr["AccountNo"].ToString(),
                        rdr["Status"].ToString(),
                        rdr["TID"].ToString(),
                        rdr["Tno"].ToString(),
                        Convert.ToInt32(rdr["Time"].Equals(DBNull.Value) ? 0 : rdr["Time"]),
                        rdr["OrderType"].ToString(),
                        Convert.ToInt64(rdr["Volume"].Equals(DBNull.Value) ? 0 : rdr["Volume"]),
                        Convert.ToDouble(rdr["Val"].Equals(DBNull.Value) ? 0 : rdr["Val"]),
                        Convert.ToInt64(rdr["MATCHED"].Equals(DBNull.Value) ? 0 : rdr["MATCHED"]),
                        Convert.ToDouble(rdr["MVal"].Equals(DBNull.Value) ? 0 : rdr["MVal"]));
                    list.Add(item);
                }
                return list;
            }
            catch
            {
                throw;
            }
        }

        public HoseMarketSummaryData getHoseMarketSummary(DateTime fromDate, DateTime toDate, string orderNo, string side, string symbol, long HighPrice, long LowPrice, string PC, string accountNo, string status, string TID, string Tno, string orderType, int fromTime, int toTime)
        {
            try
            {
                string query = HOSE_MARKET_SUMMARY + parmBuilderTransDate(fromDate, toDate) + parmBuilderOrderNo(orderNo) + parmBuilderOrderSide(side) + parmBuilderStockSymbol(symbol) + parmBuilderPrice(HighPrice, LowPrice) + parmBuilderPC(PC) + parmBuilderAccountNo(accountNo) + parmBuilderStatus(status) + parmBuilderTID(TID) + parmBuilderTno(Tno) + parmBuilderTime(fromTime, toTime) + parmBuilderOrderType(orderType);
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new HoseMarketSummaryData(
                        Convert.ToInt64((rdr["SUM_VOL"] != DBNull.Value) ? rdr["SUM_VOL"] : 0),
                        Convert.ToDouble((rdr["SUM_VAL"] != DBNull.Value) ? rdr["SUM_VAL"] : 0),
                        Convert.ToInt64((rdr["SUM_MVOL"] != DBNull.Value) ? rdr["SUM_MVOL"] : 0),
                        Convert.ToDouble((rdr["SUM_MVAL"] != DBNull.Value) ? rdr["SUM_MVAL"] : 0));
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }

        }

        private string parmBuilderTransDate(DateTime fromDate, DateTime toDate)
        {
            if (fromDate.Equals(toDate))
                return " AND (DATEDIFF(day, '" + fromDate.ToString("MM/dd/yyyy") + "', TransDate)= 0) ";
            else
                return " AND (DATEDIFF(day, TransDate, '" + fromDate.ToString("MM/dd/yyyy") + "')<= 0) AND (DATEDIFF(day, TransDate, '" + toDate.ToString("MM/dd/yyyy") + "')>= 0)";
        }
        private string parmBuilderOrderNo(string orderNo)
        {
            if (orderNo != null)
                return " AND orderNo LIKE '%" + orderNo + "%' ";
            else
                return String.Empty;
        }
        private string parmBuilderOrderSide(string side)
        {
            switch (side)
            {
                case "M":
                    return " AND OrderSide = 'B' ";
                case "B":
                    return " AND OrderSide = 'S' ";
                default:
                    return String.Empty;
            }
        }
        private string parmBuilderStockSymbol(string symbol)
        {
            {
                if (symbol != null)
                    return " AND symbol LIKE '%" + symbol + "%' ";
                else
                    return String.Empty;
            }

        }
        private string parmBuilderPrice(long HighPrice, long LowPrice)
        {
            if (LowPrice.Equals(HighPrice))
                if (HighPrice.Equals(0))
                    return String.Empty;
                else
                    return " AND PRICE='" + LowPrice.ToString() + "'";
            else
                return " AND (PRICE>=" + LowPrice.ToString() + ") AND (PRICE <=" + HighPrice.ToString() + ")";
        }
        private string parmBuilderQtty(long highQtty, long lowQtty)
        {
            if (lowQtty.Equals(highQtty))
                if (highQtty.Equals(0))
                    return String.Empty;
                else
                    return " AND VOLUME='" + lowQtty.ToString() + "'";
            else
                return " AND (VOLUME >=" + lowQtty.ToString() + ") AND (VOLUME <=" + highQtty.ToString() + ")";

        }
        private string parmBuilderMQtty(long highMQtty, long lowMQtty)
        {
            if (lowMQtty.Equals(highMQtty))
                if (highMQtty.Equals(0))
                    return String.Empty;
                else
                    return " AND MATCHED='" + lowMQtty.ToString() + "'";
            else
                return " AND (MATCHED >=" + lowMQtty.ToString() + ") AND (MATCHED <=" + highMQtty.ToString() + ")";

        }
        private string parmBuilderVal(double highVal, double lowVal)
        {
            if (lowVal.Equals(highVal))
                if (highVal.Equals(0))
                    return String.Empty;
                else
                    return " AND Val='" + lowVal.ToString() + "'";
            else
                return " AND (Val >=" + lowVal.ToString() + ") AND (Val <=" + highVal.ToString() + ")";

        }
        private string parmBuilderMVal(double high, double low)
        {
            if (low.Equals(high))
                if (high.Equals(0))
                    return String.Empty;
                else
                    return " AND Mval='" + low.ToString() + "'";
            else
                return " AND (Mval >=" + low.ToString() + ") AND (Mval <=" + high.ToString() + ")";

        }
        private string parmBuilderPC(string PC)
        {
            string temp = String.Empty;
            if (PC != null)
            {
                string[] pc = PC.Split('|');
                foreach (string item in pc)
                    temp += parmBuilderPcHelper(item);
            }
            return temp;
        }
        private string parmBuilderPcHelper(string PC)
        {
            switch (PC)
            {
                case "C":
                    return " AND (PCFLAG='C') ";
                case "M":
                    return " AND (PCFLAG='M') ";
                case "P":
                    return " AND (PCFLAG='P') ";
                case "F":
                    return " AND (PCFLAG='F') ";
                default:
                    return String.Empty;
            }

        }
        private string parmBuilderAccountNo(string accountNo)
        {
            if (accountNo != null)
                return " AND accountNo LIKE '%" + accountNo + "%' ";
            else
                return String.Empty;

        }
        private string parmBuilderStatusHelper(string status)
        {
            switch (status)
            {
                case "M":
                    return " AND (STATUS='M') ";
                case "O":
                    return " AND (STATUS='0') ";
                case "C":
                    return " AND (STATUS='C') ";
                default:
                    return String.Empty;
            }
        }
        private string parmBuilderStatus(string Status)
        {
            string temp = String.Empty;
            if (Status != null)
            {
                string[] status = Status.Split('|');
                foreach (string item in status)
                    temp += parmBuilderStatusHelper(item);
            }
            return temp;
        }
        private string parmBuilderTID(string Shortname)
        {
            if (Shortname != null)
                return " AND (ShortName = '" + Shortname + "') ";
            else
                return String.Empty;
        }
        private string parmBuilderTno(string Tno)
        {
            if (Tno != null)
                return " AND (Tno='" + Tno + "') ";
            else
                return String.Empty;
        }
        private string parmBuilderOrderType(string orderType)
        {
            switch (orderType)
            {
                case "LO":
                    return " AND [OrderType] = 'LO' ";
                case "ATO":
                    return " AND [OrderType] = 'ATO' ";
                case "ATC":
                    return " AND [OrderType] = 'ATC' ";
                default:
                    return String.Empty;
            }
        }
        private string parmBuilderTime(long fromTime, long toTime)
        {
            if (fromTime.Equals(toTime))
                if (toTime.Equals(0))
                    return String.Empty;
                else
                    return " AND ([Time]='" + fromTime.ToString() + "')";
            else
                return " AND ([Time] >=" + fromTime.ToString() + ") AND ([Time] <=" + toTime.ToString() + ")";
        }

        public void InsertDailyOrder(int firmno, DateTime transdate, string pcflag, string orderside, string symbol, int volume, int matched, int price, int time)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter("@FIRMNO" , SqlDbType.SmallInt),
                        new SqlParameter("@TRANSDATE" , SqlDbType.DateTime),
                        new SqlParameter("@PCFLAG", SqlDbType.Char, 1),
                        new SqlParameter("@ORDERSIDE", SqlDbType.Char,1),
                        new SqlParameter("@SYMBOL", SqlDbType.VarChar,8),
                        new SqlParameter("@VOLUME", SqlDbType.Int),
                        new SqlParameter("@MATCHED", SqlDbType.Int),
                        new SqlParameter("@PRICE", SqlDbType.Int),
                        new SqlParameter("@TIME", SqlDbType.Int)
                };
                parms[0].Value = firmno;
                parms[1].Value = transdate;
                parms[2].Value = pcflag;
                parms[3].Value = orderside;
                parms[4].Value = symbol;
                parms[5].Value = volume;
                parms[6].Value = matched;
                parms[7].Value = price;
                parms[8].Value = time;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertDailyOrder", parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertDailyOrderWithAccountNo(int firmno, DateTime transdate, string pcflag, string orderside, string symbol, int volume, int matched, int price, int time, string accountno)
        {
            try
            {
                SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter("@FIRMNO" , SqlDbType.SmallInt),
                        new SqlParameter("@TRANSDATE" , SqlDbType.DateTime),
                        new SqlParameter("@PCFLAG", SqlDbType.Char, 1),
                        new SqlParameter("@ORDERSIDE", SqlDbType.Char,1),
                        new SqlParameter("@SYMBOL", SqlDbType.VarChar,8),
                        new SqlParameter("@VOLUME", SqlDbType.Int),
                        new SqlParameter("@MATCHED", SqlDbType.Int),
                        new SqlParameter("@PRICE", SqlDbType.Int),
                        new SqlParameter("@TIME", SqlDbType.Int),
                        new SqlParameter(PARM_ACCOUNTNO, SqlDbType.VarChar)
                };
                parms[0].Value = firmno;
                parms[1].Value = transdate;
                parms[2].Value = pcflag;
                parms[3].Value = orderside;
                parms[4].Value = symbol;
                parms[5].Value = volume;
                parms[6].Value = matched;
                parms[7].Value = price;
                parms[8].Value = time;
                parms[9].Value = accountno;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "InsertDailyOrderWithAccountNo", parms);
            }
            catch
            {
                throw;
            }
        }


        public List<DailyOrderData> getDailyOrder(DateTime fromdate, DateTime todate, string firmno, string pcflag, string orderside, string symbol, int lowVolume, int highVolume, int lowMATCHED, int hightMATCHED)
        {
            try
            {
                string query = DAILYORDER +
                    parmBuilderTransDate(fromdate, todate) +
                    parmBuilderOrderSide(orderside) +
                    parmBuilderStockSymbol(symbol) +
                    parmBuilderPC(pcflag) +
                    parmBuilderTID(firmno) +
                    parmBuilderQtty(highVolume, lowVolume) +
                    parmBuilderMQtty(hightMATCHED, lowMATCHED);
                List<DailyOrderData> list = new List<DailyOrderData>();
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);

                while (rdr.Read())
                {
                    DailyOrderData item = new DailyOrderData(
                        Convert.ToInt32(rdr["FirmNo"]),
                        Convert.ToDateTime(rdr["TransDate"]),
                        rdr["PcFlag"].ToString(),
                        rdr["OrderSide"].ToString(),
                        rdr["Symbol"].ToString(),
                        Convert.ToInt32(rdr["Volume"]),
                        Convert.ToInt32(rdr["Matched"]),
                        Convert.ToInt32(rdr["Price"]),
                        Convert.ToInt32(rdr["Time"]));
                    list.Add(item);
                }
                return list;
            }
            catch
            {
                throw;
            }
        }

        public List<DailyOrderData> getDailyOrderWithAccountNo(DateTime fromdate, DateTime todate, string firmno, string pcflag, string orderside, string symbol, int lowVolume, int highVolume, int lowMATCHED, int hightMATCHED, string accountno)
        {
            try
            {
                string query = DAILYORDER +
                    parmBuilderTransDate(fromdate, todate) +
                    parmBuilderOrderSide(orderside) +
                    parmBuilderStockSymbol(symbol) +
                    parmBuilderPC(pcflag) +
                    parmBuilderTID(firmno) +
                    parmBuilderQtty(highVolume, lowVolume) +
                    parmBuilderMQtty(hightMATCHED, lowMATCHED) +
                    parmBuilderAccountNo(accountno);

                List<DailyOrderData> list = new List<DailyOrderData>();
                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);

                while (rdr.Read())
                {
                    DailyOrderData item = new DailyOrderData(
                        Convert.ToInt32(rdr["FirmNo"]),
                        Convert.ToDateTime(rdr["TransDate"]),
                        rdr["PcFlag"].ToString(),
                        rdr["OrderSide"].ToString(),
                        rdr["Symbol"].ToString(),
                        Convert.ToInt32(rdr["Volume"]),
                        Convert.ToInt32(rdr["Matched"]),
                        Convert.ToInt32(rdr["Price"]),
                        Convert.ToInt32(rdr["Time"]),
                        rdr["accountNo"].ToString());
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }


        public DailyOrderSummaryData getDailyOrderSummary(DateTime fromdate, DateTime todate, string firmno, string pcflag, string orderside, string symbol, int lowVolume, int highVolume, int lowMATCHED, int hightMATCHED)
        {
            try
            {
                string query = DAILYORDER_SUMMARY +
                    parmBuilderTransDate(fromdate, todate) +
                    parmBuilderOrderSide(orderside) +
                    parmBuilderStockSymbol(symbol) +
                    parmBuilderPC(pcflag) +
                    parmBuilderTID(firmno) +
                    parmBuilderQtty(highVolume, lowVolume) +
                    parmBuilderMQtty(hightMATCHED, lowMATCHED);

                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new DailyOrderSummaryData(
                        Convert.ToInt64((rdr["SUM_VOL"] != DBNull.Value) ? rdr["SUM_VOL"] : 0),
                        Convert.ToInt64((rdr["SUM_MVOL"] != DBNull.Value) ? rdr["SUM_MVOL"] : 0));
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
        }

        public DailyOrderSummaryData getDailyOrderSummaryWithAccountNo(DateTime fromdate, DateTime todate, string firmno, string pcflag, string orderside, string symbol, int lowVolume, int highVolume, int lowMATCHED, int hightMATCHED, string accountno)
        {
            try
            {
                string query = DAILYORDER_SUMMARY +
                    parmBuilderTransDate(fromdate, todate) +
                    parmBuilderOrderSide(orderside) +
                    parmBuilderStockSymbol(symbol) +
                    parmBuilderPC(pcflag) +
                    parmBuilderTID(firmno) +
                    parmBuilderQtty(highVolume, lowVolume) +
                    parmBuilderMQtty(hightMATCHED, lowMATCHED) +
                    parmBuilderAccountNo(accountno);

                SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new DailyOrderSummaryData(
                        Convert.ToInt64((rdr["SUM_VOL"] != DBNull.Value) ? rdr["SUM_VOL"] : 0),
                        Convert.ToInt64((rdr["SUM_MVOL"] != DBNull.Value) ? rdr["SUM_MVOL"] : 0));
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
        }


        public void DeleteDailyOrderByDate(DateTime transDate)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_TRANSDATE, SqlDbType.DateTime);
                parm1.Value = transDate;

                SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.StoredProcedure, "DeleteDailyOrderByDate", parm1);
            }
            catch
            {
                throw;
            }
        }

    }
}
